package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Phone;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.List;

/**
 * @author sjp
 * @date 2021/5/8 14:30
 * @description 题目2
 * 需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
 * 需求2:  查询所有颜色是白色的手机信息
 */
public class QueryTest {

    /**
     * 查询价格高于2000元，生产日期是2019年之前的所有手机
     */
    @Test
    public void query1() {
        try {
            //1.获取QueryRunner对象 自动管理方式
            QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写sql 执行查询
            String sql = "select * from phone where price>2000 and year(prodate)<2019;";
            List<Phone> phoneList = runner.query(sql, new BeanListHandler<Phone>(Phone.class));

            //3.遍历结果集
            for (Phone phone : phoneList) {
                System.out.println(phone.toString());;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 查询所有颜色是白色的手机信息
     */
    @Test
    public void query2() {
        try {
            //1.获取QueryRunner对象 自动管理方式
            QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写sql 执行查询
            String sql = "select * from phone where color='白色';";
            List<Phone> phoneList = runner.query(sql, new BeanListHandler<Phone>(Phone.class));

            //3.遍历结果集
            for (Phone phone : phoneList) {
                System.out.println(phone.toString());;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
